Conditional Formatting

[Dave]

Guys,

I'm wanting to change the colour of a cell based on it's current
value, but unsure of how to proceed with VB. Conditional formatting
only allows three options.

This does not need to be "Range Based" as I would like this to occur
in any cell in any sheet of the current workbook when a cell is
modified. ie) event based, for example if I was to type in "A" then
the cell that I have just modified would turn blue, "B", Green etc...
for nine different characters - nine different colours....

Your help would be much appreciated.

Dave.

[J.E. McGimpsey]

One way:

You'll need a Workbook SheetChange event macro (right click on the
workbook title bar, choose View Code, and paste the following into the
ThisWorkbook module). Change the values in charArr and colorArr as you
like:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim cell As Range
Dim charArr As Variant
Dim colorArr As Variant
Dim i As Long

'Substitute your preferred characters here:
charArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")

'Substitute your preferred colorindexes and order here:
colorArr = Array(3, 4, 5, 6, 7, 8, 9, 10, 11)

For Each cell In Target
With cell
.Interior.ColorIndex = xlColorIndexNone
For i = 0 To UBound(charArr)
If .Value = charArr(i) Then
.Interior.ColorIndex = colorArr(i)
Exit For
End If
Next i
End With
Next cell
End Sub

In article <526e114e.02020...@posting.google.com>, Dave
<promet...@excite.com.au> wrote:

--
Email address ROT13 encoded. Decode for real address.

[David McRitchie]

Hi Dave, and J.E.,
Those array statements look neater than Case statements
on my site; however, I think
some choices of colors might be in order see
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm

"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote in message news:070220020403090081%ar...@zptvzcfrl.pbz...

[J.E. McGimpsey]

Couldn't agree with you more, David - I are not a designer, by any
stretch of the imagination...

The sequential run of colors I used is garish to my eye, but not
everyone shares my prediliction for purple.

In article <uDwuMBDsBHA.2684@tkmsftngp05>, David McRitchie
<dmcri...@msn.com> wrote:

> Hi Dave, and J.E.,
> Those array statements look neater than Case statements
> on my site; however, I think
> some choices of colors might be in order see
> Color Palette -- the 56 Excel Colors
> http://www.mvps.org/dmcritchie/excel/colors.htm
>

--

[Dave]

J.E.

Thanks very much for this - exactly what I wanted. Worked a treat.

Dave.

"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote in message news:<070220020403090081%ar...@zptvzcfrl.pbz>...